using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;

namespace Smallpointer.Data.Common
{
    internal class ScriptUtil : DBase
    {
        public DataMapUtil MapUtil { get; private set; }
        public string Sql { get; set; }
        public List<DbParameter> Parameters { get; set; }
        private const string SQL_INSERT = "INSERT INTO {0}({1}) VALUES({2})";
        private const string SQL_UPDATE = "UPDATE {0} SET {1} WHERE {2}";
        private const string SQL_DELETE = "DELETE FROM {0} WHERE {1}";
        private const string SQL_SELECT = "SELECT {0} FROM {1} WHERE {2}";
        private const string SQL_CREATETABLE = "CREATE TABLE {0} ({1}) ON [PRIMARY]";
        private const string SQL_DROPTABLE = "DROP TABLE {0}";
        public ScriptUtil(Data parent)
            : base(parent)
        {
            MapUtil = new DataMapUtil(Parent);
        }

        public void Execute(ExecuteMode mode)
        {
            switch (mode)
            {
                case ExecuteMode.Insert:
                    var insert = new Insert(this);
                    Sql = insert.Sql;
                    Parameters = insert.Parameters;
                    break;
                case ExecuteMode.Delete:
                    var Delete = new Delete(this);
                    Sql = Delete.Sql;
                    Parameters = Delete.Parameters;
                    break;
                case ExecuteMode.Update:
                    var Update = new Update(this);
                    Sql = Update.Sql;
                    Parameters = Update.Parameters;
                    break;
                case ExecuteMode.Select:
                    var Select = new Select(this);
                    Sql = Select.Sql;
                    Parameters = Select.Parameters;
                    break;
                case ExecuteMode.CreateTable:
                    var CTable = new Table(this);
                    CTable.Create(mode);
                    Sql = CTable.Sql;
                    Parameters = CTable.Parameters;
                    break;
                case ExecuteMode.DropTable:
                    var DTable = new Table(this);
                    DTable.Create(mode);
                    Sql = DTable.Sql;
                    Parameters = DTable.Parameters;
                    break;
                default:
                    throw new ArgumentOutOfRangeException("mode");
            }
        }

        internal abstract class ScriptBase
        {
            public ScriptUtil Parent { get; set; }
            public string Sql { get; set; }
            public List<DbParameter> Parameters { get; set; }

            protected ScriptBase(ScriptUtil parent)
            {
                Parent = parent;
                Parameters = new List<DbParameter>();
            }
        }

        internal class Insert : ScriptBase
        {
            public Insert(ScriptUtil parent)
                : base(parent)
            {
                Create();
            }

            protected void Create()
            {
                var table = Parent.MapUtil.ValidInputDataMap.Name;
                var columns = string.Empty;
                var values = string.Empty;
                var i = 0;
                foreach (var keyValuePair in Parent.MapUtil.ValidInputDataMap)
                {
                    var prefix = i == 0 ? "" : ",";
                    columns += prefix + keyValuePair.Key;
                    values += prefix + "@" + keyValuePair.Key;
                    var parameter = Parent.GetSqlParameter();
                    parameter.ParameterName = "@" + keyValuePair.Key;
                    parameter.IsNullable = Parent.MapUtil.EntityDataMap[keyValuePair.Key].isnull;
                    parameter.Value = Parent.ConvertDbValue(Parent.MapUtil.EntityDataMap[keyValuePair.Key].datatype, keyValuePair.Value);
                    Parameters.Add(parameter);
                    i++;
                }
                Sql = string.Format(SQL_INSERT, table, columns, values);
            }
        }

        internal class Delete : ScriptBase
        {
            public Delete(ScriptUtil parent)
                : base(parent)
            {
                Create();
            }

            void Create()
            {
                var table = Parent.MapUtil.ValidInputDataMap.Name;
                var where = string.Empty;
                if (Parent.MapUtil.ValidConditionMap == null)
                {
                    where = "1=1";
                }
                else
                {
                    var i = 0;
                    foreach (var keyValuePair in Parent.MapUtil.ValidInputDataMap)
                    {
                        var prefix = i == 0 ? "" : " AND ";
                        where += prefix + keyValuePair.Key + "=@" + keyValuePair.Key;
                        var parameter = Parent.GetSqlParameter();
                        parameter.ParameterName = "@" + keyValuePair.Key;
                        parameter.Value = Parent.ConvertDbValue(Parent.MapUtil.EntityDataMap[keyValuePair.Key].datatype, keyValuePair.Value);
                        Parameters.Add(parameter);
                        i++;
                    }
                }
                Sql = string.Format(SQL_DELETE, table, where);
            }
        }

        internal class Update : ScriptBase
        {
            public Update(ScriptUtil parent)
                : base(parent)
            {
                Create();
            }

            private void Create()
            {
                var table = Parent.MapUtil.ValidInputDataMap.Name;
                var setStatment = string.Empty;
                var where = string.Empty;
                var i = 0;
                foreach (var keyValuePair in Parent.MapUtil.ValidInputDataMap)
                {
                    var prefix = i == 0 ? "" : " , ";
                    setStatment += prefix + keyValuePair.Key + "=@" + keyValuePair.Key;
                    var parameter = Parent.GetSqlParameter();
                    parameter.ParameterName = "@" + keyValuePair.Key;
                    parameter.Value = Parent.ConvertDbValue(Parent.MapUtil.EntityDataMap[keyValuePair.Key].datatype, keyValuePair.Value);
                    Parameters.Add(parameter);
                    i++;
                }
                if (Parent.MapUtil.ValidConditionMap == null)
                {
                    where = "1=1";
                }
                else
                {
                    i = 0;
                    foreach (var keyValuePair in Parent.MapUtil.ValidInputDataMap)
                    {
                        var prefix = i == 0 ? "" : " AND ";
                        where += prefix + keyValuePair.Key + "=@condition_" + keyValuePair.Key;
                        var parameter = Parent.GetSqlParameter();
                        parameter.ParameterName = "@condition_" + keyValuePair.Key;
                        parameter.Value = Parent.ConvertDbValue(Parent.MapUtil.EntityDataMap[keyValuePair.Key].datatype, keyValuePair.Value);
                        Parameters.Add(parameter);
                        i++;
                    }
                }
                Sql = string.Format(SQL_UPDATE, table, setStatment, where);
            }


        }

        internal class Select : ScriptBase
        {
            public Select(ScriptUtil parent)
                : base(parent)
            {
                Create();
            }

            private void Create()
            {
                var table = Parent.MapUtil.ValidInputDataMap.Name;
                var columns = "*";
                var where = string.Empty;
                if (Parent.MapUtil.ValidConditionMap == null)
                {
                    where += "1=1";
                }
                else
                {
                    var i = 0;
                    foreach (var keyValuePair in Parent.MapUtil.ValidInputDataMap)
                    {
                        var prefix = i == 0 ? "" : " AND ";
                        where += prefix + keyValuePair.Key + "=@condition_" + keyValuePair.Key;
                        var parameter = Parent.GetSqlParameter();
                        parameter.ParameterName = "@condition_" + keyValuePair.Key;
                        parameter.Value = Parent.ConvertDbValue(Parent.MapUtil.EntityDataMap[keyValuePair.Key].datatype, keyValuePair.Value);
                        Parameters.Add(parameter);
                        i++;
                    }
                }
                Sql = string.Format(SQL_SELECT, columns, table, where);
            }
        }

        internal class Table : ScriptBase
        {
            public Table(ScriptUtil parent)
                : base(parent)
            {
            }
            public void Create(ExecuteMode mode)
            {
                var table = Parent.MapUtil.ValidInputDataMap.Name;
                switch (mode)
                {
                    case ExecuteMode.CreateTable:
                        var columns = string.Empty;
                        var i = 0;
                        foreach (var datamap in Parent.MapUtil.EntityDataMap)
                        {
                            var prefix = i == 0 ? "" : " , ";
                            var data = datamap.Value;
                            var code = Parent.GetDbType(datamap.Value.datatype);
                            columns += prefix + string.Format("{0}{1}{2}{3}{4}{5}",
                                data.key,
                                string.Format(" [{0}]{1}", code, (int.Parse(data.length) > 0 ? "(" + data.length + ")" : "")),
                                string.Format("{0}", data.identity ? " IDENTITY(1,1)" : ""),
                                string.Format("{0}", data.isnull ? " NULL" : " NOT NULL"),
                                string.Format("{0}", data.isprimary ? " PRIMARY KEY" : ""),
                                string.Format("{0}", data.isprimary ? "" : (string.Format("{0}", string.IsNullOrEmpty(data.defualt) ? "" : "DEFAULT (" + data.defualt + ")")))
                                );
                            i++;
                        }
                        Sql = string.Format(SQL_CREATETABLE, table, columns);
                        break;
                    case ExecuteMode.DropTable:
                        Sql = string.Format(SQL_DROPTABLE, table);
                        break;
                    default:
                        throw new ArgumentOutOfRangeException("mode", "table object can not hanlder the execute mode:" + mode);
                }
            }
        }

        public override void Dispose()
        {

        }

        public DbParameter GetSqlParameter()
        {
            var type = Parent.DBConfig.DBType;
            {
                if (type == ConnType.None || type == ConnType.MySql || type == ConnType.Sqlite)
                {
                    throw new DBConcurrencyException("Core does not support database type :" + type);
                }
            }
            DbParameter parameter;
            switch (type)
            {
                case ConnType.None:
                    parameter = null;
                    break;
                case ConnType.MsSql:
                    parameter = new SqlParameter();
                    break;
                case ConnType.MySql:
                    parameter = null;
                    break;
                case ConnType.Oracle:
                    parameter = new OracleParameter();
                    break;
                case ConnType.Access:
                    parameter = new OleDbParameter();
                    break;
                case ConnType.Sqlite:
                    parameter = null;
                    break;
                default:
                    parameter = null;
                    break;
            }
            return parameter;
        }

        public object ConvertDbValue(string keyCode, object value)
        {
            object temp;
            try
            {
                temp = Enum.Parse(typeof(ConnType), keyCode);
            }
            catch (Exception ex)
            {
                throw new ArgumentOutOfRangeException("Invalidate data type", ex);
            }
            if (temp == null)
            {
                throw new ArgumentOutOfRangeException("keyCode");
            }
            var code = (DataTypeMode)temp;
            switch (code)
            {
                case DataTypeMode.Binary:
                    break;
                case DataTypeMode.VarBinary:
                    break;
                case DataTypeMode.Bit:
                    bool boo;
                    bool.TryParse(value.ToString(), out boo);
                    temp = boo;
                    break;
                case DataTypeMode.DateTime:
                    DateTime date;
                    DateTime.TryParse(value.ToString(), out date);
                    temp = date;
                    break;
                case DataTypeMode.Date:
                    DateTime date1;
                    DateTime.TryParse(value.ToString(), out date1);
                    temp = date1;
                    break;
                case DataTypeMode.Decimal:
                    decimal de;
                    decimal.TryParse(value.ToString(), out de);
                    temp = de;
                    break;
                case DataTypeMode.Float:
                    float ft;
                    float.TryParse(value.ToString(), out ft);
                    temp = ft;
                    break;
                case DataTypeMode.Int:
                    int it;
                    int.TryParse(value.ToString(), out it);
                    temp = it;
                    break;
                case DataTypeMode.Money:
                    double dou;
                    double.TryParse(value.ToString(), out dou);
                    temp = dou;
                    break;
                case DataTypeMode.NChar:
                    temp = value.ToString();
                    break;
                case DataTypeMode.VarChar:
                    temp = value.ToString();
                    break;
                case DataTypeMode.NText:
                    temp = value.ToString();
                    break;
                case DataTypeMode.Text:
                    temp = value.ToString();
                    break;
                case DataTypeMode.NVarChar:
                    temp = value.ToString();
                    break;
                case DataTypeMode.UniqueIdentifier:
                    var guid = new Guid(value.ToString());
                    temp = guid;
                    break;
                case DataTypeMode.None:
                    temp = value;
                    break;
                default:
                    throw new ArgumentOutOfRangeException("keyCode");
            }
            return temp;
        }

        public DataTypeMode GetDbType(string code)
        {
            object temp;
            try
            {
                temp = Enum.Parse(typeof(ConnType), code);
            }
            catch (Exception ex)
            {
                throw new ArgumentOutOfRangeException("Invalidate data type", ex);
            }
            if (temp == null)
            {
                throw new ArgumentOutOfRangeException("code");
            }
            return (DataTypeMode)temp;
        }
    }
}